import time

import pyreadstat
import pandas as pd
import os

from src.common.util.mysqlUtil import MySqlUtil

class DataBase_Sav():
    def __init__(self, mysqlConfig):
        mysql = MySqlUtil(mysqlConfig)
        self.conn = mysql.conn

    def naire(self, status): #找出所有问卷
        data = pd.read_sql('select id,title from naire where status in %s'%status, self.conn)
        data = data.to_json(orient="records", force_ascii=False)
        n_idList = []
        titleList = []
        naireDict = {}
        _naireDict = {}
        for i in eval(data):
            n_id = i['id']
            n_idList.append(n_id)
            title = '%d_%s'%(i['id'], i['title'])
            titleList.append(title)
            naireDict[i['id']] = i['title']
            _naireDict['%d_%s'%(i['id'], i['title'])] = i['id']

        return n_idList, titleList, naireDict, _naireDict

    def department(self): #找出所有组织
        user_dep = pd.read_sql('select depId from user_dep where u_id=2', self.conn)
        user_dep = eval(user_dep.to_json(orient="values", force_ascii=False))

        userDep = []
        for item in user_dep:
            if len(item) == 0: continue
            userDep.append(item[0])

        data = pd.read_sql('select id,name from department', self.conn)
        data = data.to_json(orient="records", force_ascii=False)
        d_idList = []
        d_nameList = []
        departmentDict = {}
        _departmentDict = {}
        for i in eval(data):
            d_id = i['id']
            if (d_id not in userDep): continue
            d_idList.append(d_id)
            d_name = i['name']
            d_nameList.append(d_name)
            departmentDict[i['id']] = i['name']
            _departmentDict[i['name']] = i['id']

        return d_idList, d_nameList, departmentDict, _departmentDict

    def setupSav(self,path,n_id,s_name,table,naireDict, job="students", name="number"):
        self.question = ""
        variatesql = rf"select question.q_id,content,q_type,o_id,o_value from options,question where question.q_id=options.q_id and question.n_id={n_id} and options.n_id={n_id} order by question.q_id"
        variatedata = self.selectData(variatesql)
        valuedict, labledict, qid_min_dict = self.variateDataframe2Json(variatedata)
        # print(valuedict, labledict, qid_min_dict)

        sql = rf"select {name},{table}.u_id,{table}.q_id,q_type,o_id,o_addtion from {table},question,students where {table}.q_id=question.q_id and {table}.u_id = {job}.id and {table}.n_id = {n_id} and question.n_id={n_id} order by {table}.u_id"
        # print(sql)
        data = self.selectData(sql)
        # print(data)
        json_list = self.Dataframe2Json(data, qid_min_dict, name)
        if (json_list==None or json_list==[{}]): return
        # print(json_list)
        df, lablelist = self.Json2Dataframe(json_list)
        # print(sql)
        # print(df)

        lableList = self.variateTable(labledict, lablelist)

        # self.writeSav(df, path, s_name, n_id, naireDict, valuedict, lableList)

        self.writeCsv(df, path, s_name, n_id, naireDict)

        # self.writeCsv(data, n_id, '原始')
        # self.writeCsv(df, n_id, '处理')


    def selectData(self,sql): #获取问卷记录
        data = pd.read_sql(sql, self.conn)
        return data

    def Dataframe2Json(self,data,qid_min_dict, name): #Dataframe转Json
        data_json = data.to_json(orient="records", force_ascii=False)
        # print(data_json)
        list = []
        user_dict = {}
        self.count = 1
        self.cont = 1
        for dict in eval(data_json):
            try:
                mcx = user_dict['u_id']
            except:
                user_dict['u_id'] = str(dict[name])
            if user_dict['u_id'] == str(dict[name]):
                self.dataProcessing(user_dict, dict, qid_min_dict)
            else:
                list.append(user_dict)
                user_dict = {}
                self.count = 1
                user_dict['u_id'] = str(dict[name])
                self.dataProcessing(user_dict, dict, qid_min_dict)
        list.append(user_dict)
        # print(list)
        return list
        # print(self.list_json)

    def dataProcessing(self,user_dict, dict, qid_min_dict): #json数据处理
        # print(dict['u_id'], dict['q_id'])
        if dict['q_type'] == "单选":
            try:
                into_id = int(dict['o_id']) - qid_min_dict[dict['q_id']]
                user_dict['q%d'% dict['q_id']] = into_id
            except:
                pass
        elif dict['q_type'] == "多选":
            try:
                into_id = int(dict['o_id']) - qid_min_dict[dict['q_id']]
                user_dict['q%d_o%s'%(dict['q_id'], into_id)] = 1
            except:
                pass
        elif dict['q_type'] == "填空":
            answer = "[dict['o_id']]"
            user_dict['q%d'% dict['q_id']] = eval(answer)
        elif dict['q_type'] == "排序":
            try:
                into_id = int(dict['o_id']) - int(qid_min_dict[dict['q_id']])
            except:
                into_id = 0
            mcx = 0
            if(into_id != 0):
                try:
                    mcx = user_dict['q%d_o%s'%(dict['q_id'], into_id)]
                except:
                    mcx = 1
                if dict['q_id'] != self.question:
                    self.count = 1
                if mcx == 1:
                    user_dict['q%d_o%s' % (dict['q_id'], into_id)] = self.count
                    self.count = self.count + 1
                self.question = dict['q_id']
        elif dict['q_type'] == "日期" or dict['q_type'] == "地址" or dict['q_type'] == "院校":
            user_dict['q%d' % dict['q_id']] = dict['o_id']
        else:
            pass

    def variateDataframe2Json(self, data):  # Dataframe转Json
        datajson = data.to_json(orient="records", force_ascii=False)
        # print(datajson)

        qid_min_dict = {}
        for dict in eval(datajson):
            try:
                mcx = qid_min_dict[dict['q_id']]
                pass
            except:
                qid_min_dict[dict['q_id']] = int(dict['o_id']) - 1
        jsonlist = []
        qid_dict = {}
        o_dict = {}
        for dict in eval(datajson):
            into_id = int(dict['o_id']) - qid_min_dict[dict['q_id']]
            try:
                mcx = qid_dict['q_id']
            except:
                if dict['q_type'] == "多选":
                    qid_dict['q_id'] = 'q%d_o%d' % (dict['q_id'],into_id)
                else:
                    qid_dict['q_id'] = 'q%d' % dict['q_id']
                qid_dict['q_type'] = dict['q_type']
                qid_dict['q_value'] = dict['content']
                # print(qid_dict)
            if qid_dict['q_id'] == 'q%d' % dict['q_id'] or qid_dict['q_id'] == 'q%d_o%d' % (dict['q_id'],into_id):
                pass
            else:
                qid_dict['o_values'] = o_dict
                jsonlist.append(qid_dict)
                qid_dict = {}
                o_dict = {}
                if dict['q_type'] == "多选":
                    qid_dict['q_id'] = 'q%d_o%d' % (dict['q_id'],into_id)
                elif dict['q_type'] == "排序":
                    qid_dict['q_id'] = 'q%d_o%d' % (dict['q_id'], into_id)
                else:
                    qid_dict['q_id'] = 'q%d' % dict['q_id']
                qid_dict['q_type'] = dict['q_type']
                qid_dict['q_value'] = dict['content']
            o_dict[into_id] = dict['o_value']
            # o_dict['%d'%dict['o_id']] = dict['o_value']
        qid_dict['o_values'] = o_dict
        jsonlist.append(qid_dict)
        valuedict, labledict = self.variatedataProcessing(jsonlist)
        return valuedict,labledict,qid_min_dict


    def variatedataProcessing(self, list):
        valuedict = {}
        pointdict = {}
        labledict = {'u_id': '用户列'}
        for dict in list:
            if dict['q_type'] == "填空" or dict['q_type'] == "日期" or dict['q_type'] == "地址" or dict['q_type'] == "院校":
                valuedict[dict['q_id']] = {1:"无"}
            else:
                valuedict[dict['q_id']] = dict['o_values']
                pointdict[dict['q_id']] = 'scale'
            labledict[dict['q_id']] = dict['q_value']
        return valuedict,labledict

    def variateTable(self,labledict,lablelist):
        lableList = []
        # print(labledict)
        # print(lablelist)
        for lable in lablelist:
            lableList.append(labledict[lable])
        return lableList

    def Json2Dataframe(self, list_json): #json转dataframe
        df = pd.DataFrame(list_json)
        df = df.sort_index(axis = 1, ascending = False)
        # print(df)
        tablelist = df.columns.values.tolist()
        # print(tablelist)
        return df, tablelist


    def writeSav(self,df,path,s_name,n_id,naireDict,valuedict,lableList): #dataframe数据保存为Sav文件
        """
        导出Sav文件
        :param df: dataframe数据
        :param path: 导出路径
        :param s_name: 学校名称
        :param n_id: 问卷id
        :param naireDict: 问卷 id_名称 表，用来获取title（问卷名称）
        :param valuedict: 答案列
        :param lableList: 问题列
        :return:
        """
        # print(df)
        title = naireDict[n_id].replace(r"\/", "-")
        DateTime = f"{time.strftime('%Y-%m-%d--%H-%M-%S')}"
        pyreadstat.write_sav(df, rf'{path}/{title}-{s_name}-{DateTime}.sav', None, lableList, None, None, valuedict, None, None)

    def writeCsv(self, df, path, s_name, n_id, naireDict): ##dataframe数据保存为Csv文件
        print(f'{path}/{naireDict[n_id]}-{s_name}')
        title = naireDict[n_id].replace(r"\/", "-")
        DateTime = f"{time.strftime('%Y-%m-%d--%H-%M-%S')}"
        df.to_csv(rf'{path}/{title}-{s_name}-{DateTime}.csv', index=False, header=True)


if __name__ == '__main__':
    # table = 'result47'
    # n_id = 390
    # path = '.'
    # s_name = "问卷"
    # status = str((1, 2))
    # DB = DataBase_Sav()
    # n_idList, titleList, naireDict, _naireDict = DB.naire(status)
    # DB.setupSav(path, n_id, s_name, table, naireDict)

    path = f"./NGQS_{time.strftime('%Y-%m-%d--%H-%M-%S')}版/"
    status = str((1, 2))
    DB = DataBase_Sav('database_master')
    n_idList, titleList, naireDict, _naireDict = DB.naire(status)
    d_idList, d_nameList, departmentDict, _departmentDict = DB.department()
    for d_id in d_idList:
        table = f'result{d_id}'
        departmentPath = path+f"{departmentDict[d_id]}"
        if not os.path.isdir(departmentPath):
            os.makedirs(departmentPath)
        for n_id in n_idList:
            s_name = departmentDict[d_id]
            DB.setupSav(departmentPath, n_id, s_name, table, naireDict, "students", "name")
